from sqlalchemy import func, select
from sqlalchemy.ext.asyncio import AsyncSession
from app.models import models


async def get_school_statistics(db: AsyncSession):
    """获取学校、班级、课程的统计信息"""
    stmt = (
        select(
            models.School.id.label("school_id"),
            models.School.name.label("school_name"),
            func.count(func.distinct(models.Class.id)).label("class_count"),
            func.count(func.distinct(models.Course.id)).label("course_count"),
        )
        .select_from(models.School)
        .outerjoin(models.Class, models.School.id == models.Class.school_id)
        .outerjoin(models.Course, models.Class.id == models.Course.class_id)
        .group_by(models.School.id, models.School.name)
    )
    result = await db.execute(stmt)
    return result.all()
